﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using MySql.Data.MySqlClient;
using System.Collections;
using EnvironmentManagerSystem.MODE;

namespace EnvironmentManagerSystem.BLL
{
    public class EvaluationUnit
    {

        #region 根据ID查Enabled(x)
        public static DataTable GetEnabled(int ID)
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat("SELECT * FROM tbs001_evaluationunit WHERE 1=1 and ID={0}", ID);
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion

        #region 根据ID修改启用状态(x)
        //将设置启用
        public static bool UpdateEnabled1(int ID)
        {
            try
            {
                string strSql = string.Format("UPDATE tbs001_evaluationunit set Enabled=1 WHERE ID={0}", ID);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        //将设置禁用
        public static bool UpdateEnabled2(int ID)
        {
            try
            {
                string strSql = string.Format("UPDATE tbs001_evaluationunit set Enabled=2 WHERE ID={0}", ID);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        #endregion


        public static string byteToHexStr(byte[] bytes)
        {
            string returnStr = "";
            if (bytes != null)
            {
                for (int i = 0; i < bytes.Length; i++)
                {
                    returnStr += bytes[i].ToString("X2");
                }
            }
            return returnStr;
        }
        /// <summary>
        /// 获取评价单位列表
        /// </summary>
        /// <returns></returns>
        public static EvaluationUnitDataCollection GetList()
        {
            try
            {
                EvaluationUnitDataCollection list = new EvaluationUnitDataCollection();
                string strSql = @"SELECT * FROM `TBS001_EVALUATIONUNIT` order by UnitName";
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        EvaluationUnitData model = new EvaluationUnitData();
                        model = BindDataReader(dr);
                        model.Enabled = Convert.ToInt32(dr["Enabled"]);
                        list.Add(model);
                    }
                }
                return list;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取项目负责人
        /// </summary>
        /// <returns></returns>
        public static EvaluationUnitData GetModel()
        {
            try
            {
                string strSql = string.Format(@"SELECT * FROM `TBS001_EVALUATIONUNIT` ORDER BY ID DESC LIMIT 1");
                EvaluationUnitData model = new EvaluationUnitData();
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        model = BindDataReader(dr);
                    }
                }
                return model;
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 获取评价单位列表
        /// </summary>
        /// <returns></returns>
        public static EvaluationUnitData GetModel(int ID)
        {
            try
            {
                string strSql = string.Format(@"SELECT a.*,b.LoginName,b.PASSWORD FROM TBS001_EVALUATIONUNIT a left join tbs001_user b on a.ID=b.evaluationunitID WHERE a.ID={0}", ID);
                EvaluationUnitData model = new EvaluationUnitData();
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        model = BindDataReader(dr);
                        model.LoginName = dr["LoginName"].ToString();
                        model.PassWord = dr["PASSWORD"].ToString();
                    }
                }
                return model;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 添加新评价单位
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool InsertUnit(EvaluationUnitData model)
        {
            try
            {
                string strSql = string.Format("INSERT INTO `TBS001_EVALUATIONUNIT`  (UnitName,Address,CertificateNo,Phone,ZipCode,EvaluationScope,QualificationFileURL,AllSignedURL,Enabled) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',1)",
                                                  model.UnitName, model.Address, model.CertificateNo, model.Phone, model.ZipCode, model.EvaluationScope, model.QualificationFileURL, model.AllSignedURL, model.Enabled);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        public static bool InsertUser(EvaluationUnitData model, UserData use)
        {
            string id = "";
            string ri = "环评单位";
            string iu = "1";
            DataTable dt = EvaluationUnit.Geta(model.CertificateNo);
            if (dt.Rows.Count != 0)
            {
                id = dt.Rows[0]["ID"].ToString().Trim();//ID
            }
            StringBuilder br = new StringBuilder();
            br.Append(" insert into tbs001_user (UserName,LoginName,Password,Phone,RoleId,UserKind,InUse,evaluationunitID) values ( ");
            br.AppendFormat("'{0}',", use.UserName);
            br.AppendFormat("'{0}',", use.LoginName);
            br.AppendFormat("'{0}',", use.Password);
            br.AppendFormat("'{0}',", use.Phone);
            br.AppendFormat("'{0}',",ri);
            br.AppendFormat("'{0}',",ri);
            br.AppendFormat("'{0}',",iu);
            br.AppendFormat("'{0}')", id);
            return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? true : false;
        }
        #region MyRegion
        public static DataTable Geta(string a)
        {
            try
            {
                string strSql = string.Format(@"SELECT tbs001_evaluationunit.* FROM tbs001_evaluationunit where CertificateNo='{0}'", a);
                return DbHelperMySQL.Query(strSql).Tables[0];
            }
            catch
            {
                return null;
            }
        }
        #endregion
        /// <summary>
        /// 修改评价单位
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool UpdateUnit(EvaluationUnitData model, UserData use)
        {
            try
            {
                List<string> list = new List<string>();
                string strSql = string.Format("UPDATE `TBS001_EVALUATIONUNIT` SET UnitName='{0}',Address='{1}',CertificateNo='{2}',Phone='{3}',ZipCode='{4}',EvaluationScope='{5}', QualificationFileURL='{6}',AllSignedURL='{7}' WHERE ID={8}",
                                                model.UnitName, model.Address, model.CertificateNo, model.Phone, model.ZipCode, model.EvaluationScope, model.QualificationFileURL, model.AllSignedURL, model.ID);
                StringBuilder br = new StringBuilder();
                if (DbHelperMySQL.Query("select ID from tbs001_user where evaluationunitID=" + model.ID).Tables[0].Rows.Count == 0)
                {
                    br.Append(" insert into tbs001_user (UserName,LoginName,Password,Phone,RoleId,UserKind,InUse,evaluationunitID) values ( ");
                    br.AppendFormat("'{0}',", use.UserName);
                    br.AppendFormat("'{0}',", use.LoginName);
                    br.AppendFormat("'{0}',", use.Password);
                    br.AppendFormat("'{0}','环评单位','环评单位',1,", model.Phone);
                    br.AppendFormat(" {0} )", model.ID);
                }
                else
                {
                    br.Append(" update tbs001_user set");
                    br.AppendFormat("  UserName = '{0}' ", use.UserName);
                    br.AppendFormat(" ,LoginName = '{0}' ", use.LoginName);
                    br.AppendFormat(" ,Password = '{0}' ", use.Password);
                    br.AppendFormat(" ,Phone = '{0}' ", use.Phone);
                    br.AppendFormat(" where evaluationunitID={0}", model.ID);
                }
                list.Add(strSql);
                list.Add(br.ToString());
                return DbHelperMySQL.ExecuteSqlTran(list) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 删除评价单位
        /// </summary>
        /// <param name="ID">评价单位编号</param>
        /// <returns></returns>
        public static bool DeleteUnit(int ID)
        {
            try
            {
                List<string> SQLStringList = new List<string>();
                SQLStringList.Add(string.Format(@"delete from `TBS001_EVALUATIONUNIT` where ID={0}", ID));
                SQLStringList.Add(string.Format(@"delete from `TBS001_PROJECTLEADER` where UnitID={0}", ID));
                SQLStringList.Add(string.Format(@"delete from `tbs001_user` where evaluationunitID={0}", ID));
                return DbHelperMySQL.ExecuteSqlTran(SQLStringList) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 证件号是否存在
        /// </summary>
        /// <param name="use"></param>
        /// <returns></returns>
        public static bool WhetherExistence(string CertificateNo)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format("SELECT ID FROM tbs001_evaluationunit WHERE CertificateNo='{0}'", CertificateNo)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 证件号是否存在
        /// </summary>
        /// <param name="use"></param>
        /// <returns></returns>
        public static bool WhetherExistence(string CertificateNo, int UnitID)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format("SELECT ID FROM tbs001_evaluationunit WHERE CertificateNo='{0}' and ID!={1}", CertificateNo, UnitID)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        public static EvaluationUnitData BindDataReader(MySqlDataReader dr)
        {
            EvaluationUnitData model = new EvaluationUnitData();
            model.ID = (int)dr["ID"];
            model.UnitName = dr["UnitName"].ToString();
            model.Address = dr["Address"].ToString();
            model.CertificateNo = dr["CertificateNo"].ToString();
            model.Phone = dr["Phone"].ToString();
            model.ZipCode = dr["ZipCode"].ToString();
            model.EvaluationScope = dr["EvaluationScope"].ToString();
            model.QualificationFile = Encoding.Default.GetBytes(dr["QualificationFile"].ToString());
            model.AllSigned = Encoding.Default.GetBytes(dr["AllSigned"].ToString());
            model.QualificationFileURL = dr["QualificationFileURL"].ToString();
            model.AllSignedURL = dr["AllSignedURL"].ToString();
            return model;
        }
    }
}
